﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Gao.Poker.Domain;

namespace Gao.Poker.Services
{
    public class MemberService : BaseService
    {
        private const string sql = @"SELECT A.[ID]
                                      ,[CardNo]
                                      ,[IdNo]
                                      ,[Name]
                                      ,[Address]
                                      ,[Birthday]
                                      ,[Telephone]
                                      ,[Email]
                                      ,[ConsumeMoney]
                                      ,[Points]
                                      ,[OpenCardDate]
                                      ,[Remark]
                                      ,[State]
                                      ,B.[BuyNumber]
                                      ,B.[BuyAgainNumber]
                                      ,B.[BuyLastNumber]
                                      ,B.[BuyMoney]
                                      ,B.[Rank]
                                      ,B.[Bonus]
                                      ,B.[OutState]
                                      ,C.[TableNum]
                                      ,C.[SeatingNum]                                  
                                      ,'' [SeatNum]
                                  FROM [Members] A 
                                  LEFT JOIN [CompetitionEntries] B ON A.CardNo = B.MemberCardNo
                                  LEFT JOIN [Seatings] C  ON A.CardNo = C.MemberCardNo AND B.CompetitionID = C.CompetitionID";

        private const string GAME_RECORD_SQL = @"SELECT
                                                A.[ID]
                                                ,A.[Name] [GameName]
                                                ,A.[Date]
                                                ,B.[MemberCardNo]
                                                ,B.[BuyNumber]
                                                ,B.[BuyAgainNumber]
                                                ,B.[BuyLastNumber]
                                                ,B.[BuyMoney]
                                                ,B.[Rank]
                                                ,B.[Bonus]
                                                ,B.[CreateDt] 
                                                FROM [Competitions] A, [CompetitionEntries] B WHERE A.[ID] = B.[CompetitionID]";


        public static MemberService _instance;

        public static MemberService Instance()
        {
            return _instance ?? (_instance = new MemberService());
        }

        public List<Member> SearchMembers()
        {
            return DbContext.Members.Where(a => a.State == 0).ToList();
        }

        public List<Member> SearchMembers(String name, String cardNo)
        {
            return DbContext.Members
                .Where(a => a.Name.Contains(name))
                .Where(a => a.State == 0)
                .Where(a => a.CardNo.Contains(cardNo)).ToList();
        }

        public List<Member> SearchMembers(String name,String cardNo,int pageIndex,int pageSize)
        {
            return DbContext.Members
                .Where(a=>a.Name.Contains(name))
                .Where(a => a.State == 0)
                .Where(a=>a.CardNo.Contains(cardNo)).OrderBy(a=>a.ID).Skip(pageIndex * pageSize).Take(pageSize).ToList();
        }

        public int SearchMembersTotal(String name, String cardNo)
        {
            return DbContext.Members
                .Where(a => a.Name.Contains(name))
                .Where(a => a.State == 0).Count(a => a.CardNo.Contains(cardNo));
        }


        public Member GetMember(String cardNo)
        {
            return DbContext.Members.Where(a=>a.State == 0).FirstOrDefault(a => a.CardNo.Equals(cardNo));
        }

        public List<Member> SearchMembers(int[] ids,String name)
        {
            return DbContext.Members
               .Where(a => a.State == 0)
               .Where(a => a.Name.Contains(name))
               .Where(a => !ids.Contains(a.ID)).ToList();
        }

        public List<MemberSeat> GetMemberCompetitionID(int competitionId)
        {
            var list = DbContext.Database.SqlQuery<MemberSeat>(String.Format("{0} WHERE B.CompetitionID = {1}", sql, competitionId)).ToList();
            return list;
        }

        public List<GameRecord> GetGameRecord(String cardNo)
        {
            var list = DbContext.Database.SqlQuery<GameRecord>(GAME_RECORD_SQL).Where(a => a.MemberCardNo == cardNo).ToList();
            return list;
        }

        public Member Save(Member member)
        {
            if (member == null) return null;

            if(member.ID == 0)
            {
                DbContext.Members.Add(member);
            }
            else
            {
                var currentMember = DbContext.Members.Find(member.ID);
                DbContext.Entry(currentMember).CurrentValues.SetValues(member);
            }

            Trace.WriteLine(DbContext.ToString());
            DbContext.SaveChanges();
            return member;
        }
    }
}
